package Page;

import DataClass.Student;
import DataClass.Teacher;
import Util.DatabaseUtil;
import javafx.beans.property.SimpleStringProperty;
import javafx.collections.FXCollections;
import javafx.collections.ObservableList;
import javafx.geometry.HPos;
import javafx.geometry.Insets;
import javafx.scene.Scene;
import javafx.scene.control.*;
import javafx.scene.layout.*;
import javafx.stage.FileChooser;
import javafx.stage.Modality;
import javafx.stage.Stage;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class UserManagementPage {

    private TableView<Student> studentTableView = new TableView<>();
    private ObservableList<Student> studentData = FXCollections.observableArrayList();
    private TableView<Teacher> teacherTableView = new TableView<>();
    private ObservableList<Teacher> teacherData = FXCollections.observableArrayList();
    Student newStudent = new Student();
    GridPane newStudentPane = new GridPane();
    TextField snoField = new TextField();
    TextField scNameField = new TextField();
    TextField sTypeField = new TextField();
    TextField sEnterField = new TextField();
    TextField stNoField = new TextField();
    TextField satNoField = new TextField();
    TextField sRemarkField = new TextField();
    TextField sAccountField = new TextField();
    TextField sPasswordField = new TextField();
    TextField sInField = new TextField();
    TextField sOutField = new TextField();
    Teacher newTeacher = new Teacher();
    GridPane newTeacherPane = new GridPane();
    TextField tnoField = new TextField();
    TextField ttnoField = new TextField();
    TextField tcNameField = new TextField();
    TextField tSexField = new TextField();
    TextField jtnoField = new TextField();
    TextField tQualificationField = new TextField();
    TextField tDutiesField = new TextField();
    TextField tRemarkField = new TextField();
    TextField tSituationField = new TextField();
    TextField tEducationField = new TextField();
    TextField tFirstDisciplineField = new TextField();
    TextField tSecondDisciplineField = new TextField();
    TextField tProfessionalAffiliationsField = new TextField();
    TextField tAdminPositionsCollegeField = new TextField();
    TextField tAcademicPositionsCollegeField = new TextField();

//    public static void main(String[] args) {
//        launch(args);
//    }

    public Stage getState() {
        Stage primaryStage = new Stage();
        primaryStage.setTitle("用户管理系统");

        // 创建按钮
        Button btnAdd = new Button("添加用户");
        Button btnDelete = new Button("删除用户");
        Button btnUpdate = new Button("更新用户");
        Button btnQuery = new Button("查询用户");
        Button btnDao = new Button("批量导入数据");

        // 创建添加用户框架
        ComboBox<String> addUserTypeComboBox = new ComboBox<>();
        addUserTypeComboBox.getItems().addAll("学生", "教师");
        addUserTypeComboBox.setPromptText("选择用户类型");

        setupAddStudentGridPane();
        setupAddTeacherGridPane();
        newStudentPane.setVisible(false);
        newTeacherPane.setVisible(false);

        // 创建一个 NewUserPane 用于叠放两个功能框架
        StackPane newUserPane = new StackPane(newStudentPane,newTeacherPane);
        newUserPane.setPadding(new Insets(20));

        Button saveButton = new Button("保存");
        Button addUserSubmitButton = new Button("提交");
        VBox addUserPane = new VBox(10, new Label("添加用户功能"), addUserTypeComboBox, newUserPane, saveButton, addUserSubmitButton);

        // 创建删除用户框架
        ComboBox<String> deleteUserTypeComboBox = new ComboBox<>();
        deleteUserTypeComboBox.getItems().addAll("学生", "教师");
        deleteUserTypeComboBox.setPromptText("选择用户类型");

        TextField deleteUserIdField = new TextField();
        deleteUserIdField.setPromptText("请输入学号/工号");

        Button deleteUserSubmitButton = new Button("提交");
        VBox deleteUserPane = new VBox(10, new Label("删除用户功能"), deleteUserTypeComboBox, deleteUserIdField, deleteUserSubmitButton);

        // 创建更新用户框架
        ComboBox<String> updateUserTypeComboBox = new ComboBox<>();
        updateUserTypeComboBox.getItems().addAll("学生", "教师");
        updateUserTypeComboBox.setPromptText("选择用户类型");

        TextField updateUserIdField = new TextField();
        updateUserIdField.setPromptText("请输入学号/工号");

        ComboBox<String> updateStudentComboBox = new ComboBox<>();
        updateStudentComboBox.getItems().addAll("SNo", "SCName", "SEName", "SSex", "SType", "SNation", "SEnter", "STNo", "SATNo", "SBir", "SEmail", "STele", "SRemark", "SAccount", "SPassword", "SIn", "SOut");
        updateStudentComboBox.setPromptText("选择要更改的信息");
        updateStudentComboBox.setVisible(false);

        ComboBox<String> updateTeacherComboBox = new ComboBox<>();
        updateTeacherComboBox.getItems().addAll("Tno", "TTno", "TEmail", "TCName", "TSex", "JTno", "TNation", "TUnity", "TEName", "TQualification", "TBir", "TTele", "TDuties", "TMobile", "TRemark", "TSituation", "TEducation", "TFirstDiscipline", "TSceondDiscipline", "TProfessionalAffiliations", "TAdminPositionsCollege", "TAcademicPositionsCollege");
        updateTeacherComboBox.setPromptText("选择要更改的信息");
        updateTeacherComboBox.setVisible(false);

        // 创建 tcPane 用于叠放两个下拉框
        StackPane tcPane = new StackPane(updateStudentComboBox, updateTeacherComboBox);

        TextField updateUserNewMessage = new TextField();
        updateUserNewMessage.setPromptText("请输入更新后信息");

        Button updateUserSubmitButton = new Button("提交");
        VBox updateUserPane = new VBox(10, new Label("更新用户功能"), updateUserTypeComboBox, updateUserIdField, tcPane, updateUserNewMessage, updateUserSubmitButton);

        // 创建查询用户框架
        ComboBox<String> queryUserTypeComboBox = new ComboBox<>();
        queryUserTypeComboBox.getItems().addAll("学生", "教师");
        queryUserTypeComboBox.setPromptText("选择用户类型");

        TextField queryUserIdField = new TextField();
        queryUserIdField.setPromptText("请输入学号/工号");

        Button queryUserSubmitButton = new Button("提交");

        setupStudentTableView();
        studentTableView.setItems(studentData);
        studentTableView.setVisible(false);
        setupTeacherTableView();
        teacherTableView.setItems(teacherData);
        teacherTableView.setVisible(false);

        // 创建一个 queryTCPane 用于叠放两个功能框架
        StackPane queryTCPane = new StackPane(studentTableView, teacherTableView);
        queryTCPane.setPadding(new Insets(20));

        VBox queryUserPane = new VBox(10, new Label("查询用户功能"), queryUserTypeComboBox, queryUserIdField, queryUserSubmitButton, queryTCPane);

        // 初始化框架为透明
        addUserPane.setVisible(false);
        deleteUserPane.setVisible(false);
        updateUserPane.setVisible(false);
        queryUserPane.setVisible(false);

        // 创建一个 StackPane 用于叠放四个功能框架
        StackPane stackPane = new StackPane(addUserPane, deleteUserPane, updateUserPane, queryUserPane);
        stackPane.setPadding(new Insets(20));

        // 按钮事件处理
        btnAdd.setOnAction(e -> {
            addUserPane.setVisible(true);
            deleteUserPane.setVisible(false);
            updateUserPane.setVisible(false);
            queryUserPane.setVisible(false);
        });

        btnDelete.setOnAction(e -> {
            addUserPane.setVisible(false);
            deleteUserPane.setVisible(true);
            updateUserPane.setVisible(false);
            queryUserPane.setVisible(false);
        });

        btnUpdate.setOnAction(e -> {
            addUserPane.setVisible(false);
            deleteUserPane.setVisible(false);
            updateUserPane.setVisible(true);
            queryUserPane.setVisible(false);
        });

        btnQuery.setOnAction(e -> {
            addUserPane.setVisible(false);
            deleteUserPane.setVisible(false);
            updateUserPane.setVisible(false);
            queryUserPane.setVisible(true);
        });

        btnDao.setOnAction(e -> showImportDialog());

        addUserTypeComboBox.setOnAction(e -> {
            String userType = addUserTypeComboBox.getValue();
            if (userType.equals("学生")) {
                newStudentPane.setVisible(true);
                newTeacherPane.setVisible(false);
            } else if (userType.equals("教师")) {
                newStudentPane.setVisible(false);
                newTeacherPane.setVisible(true);
            }
        });

        saveButton.setOnAction(e -> {
            String userType = addUserTypeComboBox.getValue();

            if(userType.equals("学生")){
                String sno = snoField.getText();
                String scName = scNameField.getText();
                String sType = sTypeField.getText();
                int sEnter = 0;
                String stNo = stNoField.getText();
                String satNo = satNoField.getText();
                String sRemark = sRemarkField.getText();
                String sAccount = sAccountField.getText();
                String sPassword = sPasswordField.getText();
                java.sql.Timestamp sIn = null;
                java.sql.Timestamp sOut = null;
                try {
                    if(!sEnterField.getText().isEmpty()){
                        sEnter = Integer.parseInt(sEnterField.getText());
                    }
                    if (!sInField.getText().isEmpty()) {
                        sIn = java.sql.Timestamp.valueOf(sInField.getText());
                    }
                    if (!sOutField.getText().isEmpty()) {
                        sOut = java.sql.Timestamp.valueOf(sOutField.getText());
                    }
                } catch (IllegalArgumentException ex) {
                    System.err.println("错误的日期：" + ex.getMessage());
                    return;
                }

                // 保存学生信息
                newStudent.setSNo(sno);
                newStudent.setSCName(scName);
                newStudent.setSType(sType);
                newStudent.setSEnter(sEnter);
                newStudent.setSTNo(stNo);
                newStudent.setSATNo(satNo);
                newStudent.setSRemark(sRemark);
                newStudent.setSAccount(sAccount);
                newStudent.setSPassword(sPassword);
                newStudent.setSIn(sIn);
                newStudent.setSOut(sOut);
            }
            else if(userType.equals("教师")){
                String tno = tnoField.getText();
                String ttno = ttnoField.getText();
                String tcName = tcNameField.getText();
                String tSex = tSexField.getText();
                String jtno = jtnoField.getText();
                String tQualification = tQualificationField.getText();
                String tDuties = tDutiesField.getText();
                String tRemark = tRemarkField.getText();
                String tSituation = tSituationField.getText();
                String tEducation = tEducationField.getText();
                String tFirstDiscipline = tFirstDisciplineField.getText();
                String tSecondDiscipline = tSecondDisciplineField.getText();
                String tProfessionalAffiliations = tProfessionalAffiliationsField.getText();
                String tAdminPositionsCollege = tAdminPositionsCollegeField.getText();
                String tAcademicPositionsCollege = tAcademicPositionsCollegeField.getText();

                //保存教师信息
                newTeacher.setTno(tno);
                newTeacher.setTTno(ttno);
                newTeacher.setTCName(tcName);
                newTeacher.setTSex(tSex);
                newTeacher.setJTno(jtno);
                newTeacher.setTQualification(tQualification);
                newTeacher.setTDuties(tDuties);
                newTeacher.setTRemark(tRemark);
                newTeacher.setTSituation(tSituation);
                newTeacher.setTEducation(tEducation);
                newTeacher.setTFirstDiscipline(tFirstDiscipline);
                newTeacher.setTSecondDiscipline(tSecondDiscipline);
                newTeacher.setTProfessionalAffiliations(tProfessionalAffiliations);
                newTeacher.setTAdminPositionsCollege(tAdminPositionsCollege);
                newTeacher.setTAcademicPositionsCollege(tAcademicPositionsCollege);
            }
        });


        addUserSubmitButton.setOnAction(e -> {
            String userType = addUserTypeComboBox.getValue();

            if (userType == null || userType.isEmpty()) {
                showAlert(Alert.AlertType.WARNING, "请先选择用户类型");
                return;
            }
            try{
                String res = "";
                if(userType.equals("学生")){
                    res = DatabaseUtil.addStudent(newStudent);
                }
                else if(userType.equals("教师")){
                    res = DatabaseUtil.addTeacher(newTeacher);
                }
                showAlert(Alert.AlertType.INFORMATION, res);
            } catch (SQLException ex) {
                showAlert(Alert.AlertType.ERROR, "添加失败：数据库操作错误");
                ex.printStackTrace();
            }
        });

        updateUserTypeComboBox.setOnAction(e -> {
            String userType = updateUserTypeComboBox.getValue();
            if (userType.equals("学生")) {
                updateStudentComboBox.setVisible(true);
                updateTeacherComboBox.setVisible(false);
            } else if (userType.equals("教师")) {
                updateStudentComboBox.setVisible(false);
                updateTeacherComboBox.setVisible(true);
            }
        });

        updateUserSubmitButton.setOnAction(e -> {
            String userType = updateUserTypeComboBox.getValue();
            String userId = updateUserIdField.getText();
            String newMessage = updateUserNewMessage.getText();

            if (userType == null || userType.isEmpty()) {
                showAlert(Alert.AlertType.WARNING, "请先选择用户类型");
                return;
            }

            if (userId.isEmpty()) {
                showAlert(Alert.AlertType.WARNING, "请先输入学号/工号");
                return;
            }

            try {
                String res = "";
                if ("学生".equals(userType)) {
                    res = DatabaseUtil.UpdateStudent(userId, updateStudentComboBox.getValue(), newMessage);
                } else if ("教师".equals(userType)) {
                    res = DatabaseUtil.UpdateTeacher(userId, updateTeacherComboBox.getValue(), newMessage);
                }
                showAlert(Alert.AlertType.INFORMATION, res);
            } catch (SQLException ex) {
                showAlert(Alert.AlertType.ERROR, "更新失败：数据库操作错误");
                ex.printStackTrace();
            }
        });

        deleteUserSubmitButton.setOnAction(e -> {
            String userType = deleteUserTypeComboBox.getValue();
            String userId = deleteUserIdField.getText();

            if (userType == null || userType.isEmpty()) {
                showAlert(Alert.AlertType.WARNING, "请先选择用户类型");
                return;
            }

            if (userId.isEmpty()) {
                showAlert(Alert.AlertType.WARNING, "请先输入学号/工号");
                return;
            }

            try {
                String res = "";
                if ("学生".equals(userType)) {
                    res = DatabaseUtil.DeleteStudent(userId);
                } else if ("教师".equals(userType)) {
                    res = DatabaseUtil.DeleteTeacher(userId);
                }
                showAlert(Alert.AlertType.INFORMATION, res);
            } catch (SQLException ex) {
                showAlert(Alert.AlertType.ERROR, "删除失败：数据库操作错误");
            }
        });

        queryUserTypeComboBox.setOnAction(e -> {
            String userType = queryUserTypeComboBox.getValue();
            if (userType.equals("学生")) {
                studentTableView.setVisible(true);
                teacherTableView.setVisible(false);
            } else if (userType.equals("教师")) {
                studentTableView.setVisible(false);
                teacherTableView.setVisible(true);
            }
        });

        queryUserSubmitButton.setOnAction(e -> {
            String userType = queryUserTypeComboBox.getValue();
            String No = queryUserIdField.getText();

            if (userType == null || userType.isEmpty()) {
                showAlert(Alert.AlertType.WARNING, "请先选择用户类型");
                return;
            }

            if (No != null && !No.trim().isEmpty()) {
                try {
                    if(userType.equals("学生")){
                        studentData.clear();
                        teacherData.clear();
                        Student student = DatabaseUtil.queryStudent(No);
                        if (student != null) {
                            GridPane newStudentPane = new GridPane();             studentData.add(student);
                        } else {
                            showAlert(Alert.AlertType.INFORMATION, "没有找到相关学生");
                        }
                    }
                    else if(userType.equals("教师")){
                        studentData.clear();
                        teacherData.clear();
                        Teacher teacher = DatabaseUtil.queryTeacher(No);
                        if (teacher != null) {
                            teacherData.add(teacher);
                        } else {
                            showAlert(Alert.AlertType.INFORMATION, "没有找到相关教师");
                        }
                    }
                } catch (SQLException ex) {
                    showAlert(Alert.AlertType.ERROR, "查询失败：数据库操作错误");
                    ex.printStackTrace();
                }
            } else {
                showAlert(Alert.AlertType.WARNING, "请先输入学号/工号");
            }
        });

        // 主布局
        HBox hbox = new HBox(50); // 10 是按钮之间的间距
        hbox.getChildren().addAll(btnAdd, btnDelete, btnUpdate, btnQuery, btnDao);

        VBox mainLayout = new VBox(10);
        mainLayout.setPadding(new Insets(20));
        mainLayout.getChildren().addAll(hbox, stackPane);

        Scene scene = new Scene(mainLayout, 800, 350);
        primaryStage.setScene(scene);
//        primaryStage.show();
        return primaryStage;
    }

    private void setupStudentTableView() {
        TableColumn<Student, String> snoColumn = new TableColumn<>("学号");
        snoColumn.setCellValueFactory(cellData -> new SimpleStringProperty(cellData.getValue().getSNo()));

        TableColumn<Student, String> scNameColumn = new TableColumn<>("姓名");
        scNameColumn.setCellValueFactory(cellData -> new SimpleStringProperty(cellData.getValue().getSCName()));

        TableColumn<Student, String> sTypeColumn = new TableColumn<>("类型");
        sTypeColumn.setCellValueFactory(cellData -> new SimpleStringProperty(cellData.getValue().getSType()));

        TableColumn<Student, String> sEnterColumn = new TableColumn<>("入学年份");
        sEnterColumn.setCellValueFactory(cellData -> new SimpleStringProperty(String.valueOf(cellData.getValue().getSEnter())));

        TableColumn<Student, String> stNoColumn = new TableColumn<>("系编号");
        stNoColumn.setCellValueFactory(cellData -> new SimpleStringProperty(cellData.getValue().getSTNo()));

        TableColumn<Student, String> satNoColumn = new TableColumn<>("导师编号");
        satNoColumn.setCellValueFactory(cellData -> new SimpleStringProperty(cellData.getValue().getSATNo()));

        TableColumn<Student, String> sRemarkColumn = new TableColumn<>("备注");
        sRemarkColumn.setCellValueFactory(cellData -> new SimpleStringProperty(cellData.getValue().getSRemark()));

        TableColumn<Student, String> sAccountColumn = new TableColumn<>("账号");
        sAccountColumn.setCellValueFactory(cellData -> new SimpleStringProperty(cellData.getValue().getSAccount()));

        TableColumn<Student, String> sPasswordColumn = new TableColumn<>("密码");
        sPasswordColumn.setCellValueFactory(cellData -> new SimpleStringProperty(cellData.getValue().getSPassword()));

        TableColumn<Student, String> sInColumn = new TableColumn<>("入学时间");
        sInColumn.setCellValueFactory(cellData -> {
            if (cellData.getValue().getSIn() != null) {
                return new SimpleStringProperty(cellData.getValue().getSIn().toString());
            } else {
                return new SimpleStringProperty("N/A");
            }
        });

        TableColumn<Student, String> sOutColumn = new TableColumn<>("毕业时间");
        sOutColumn.setCellValueFactory(cellData -> {
            if (cellData.getValue().getSOut() != null) {
                return new SimpleStringProperty(cellData.getValue().getSOut().toString());
            } else {
                return new SimpleStringProperty("N/A");
            }
        });

        studentTableView.getColumns().addAll(snoColumn, scNameColumn, sTypeColumn, sEnterColumn, stNoColumn, satNoColumn, sRemarkColumn, sAccountColumn, sPasswordColumn, sInColumn, sOutColumn);
    }

    private void setupTeacherTableView() {
        TableColumn<Teacher, String> tnoColumn = new TableColumn<>("工号");
        tnoColumn.setCellValueFactory(cellData -> new SimpleStringProperty(cellData.getValue().getTno()));

        TableColumn<Teacher, String> tTnoColumn = new TableColumn<>("教师编号");
        tTnoColumn.setCellValueFactory(cellData -> new SimpleStringProperty(cellData.getValue().getTTno()));

        TableColumn<Teacher, String> tCNameColumn = new TableColumn<>("姓名");
        tCNameColumn.setCellValueFactory(cellData -> new SimpleStringProperty(cellData.getValue().getTCName()));

        TableColumn<Teacher, String> tSexColumn = new TableColumn<>("性别");
        tSexColumn.setCellValueFactory(cellData -> new SimpleStringProperty(cellData.getValue().getTSex()));

        TableColumn<Teacher, String> jTnoColumn = new TableColumn<>("系编号");
        jTnoColumn.setCellValueFactory(cellData -> new SimpleStringProperty(cellData.getValue().getJTno()));

        TableColumn<Teacher, String> tQualificationColumn = new TableColumn<>("学历");
        tQualificationColumn.setCellValueFactory(cellData -> new SimpleStringProperty(cellData.getValue().getTQualification()));

        TableColumn<Teacher, String> tDutiesColumn = new TableColumn<>("职务");
        tDutiesColumn.setCellValueFactory(cellData -> new SimpleStringProperty(cellData.getValue().getTDuties()));

        TableColumn<Teacher, String> tRemarkColumn = new TableColumn<>("备注");
        tRemarkColumn.setCellValueFactory(cellData -> new SimpleStringProperty(cellData.getValue().getTRemark()));

        TableColumn<Teacher, String> tSituationColumn = new TableColumn<>("情况");
        tSituationColumn.setCellValueFactory(cellData -> new SimpleStringProperty(cellData.getValue().getTSituation()));

        TableColumn<Teacher, String> tEducationColumn = new TableColumn<>("学历");
        tEducationColumn.setCellValueFactory(cellData -> new SimpleStringProperty(cellData.getValue().getTEducation()));

        TableColumn<Teacher, String> tFirstDisciplineColumn = new TableColumn<>("第一学科");
        tFirstDisciplineColumn.setCellValueFactory(cellData -> new SimpleStringProperty(cellData.getValue().getTFirstDiscipline()));

        TableColumn<Teacher, String> tSecondDisciplineColumn = new TableColumn<>("第二学科");
        tSecondDisciplineColumn.setCellValueFactory(cellData -> new SimpleStringProperty(cellData.getValue().getTSecondDiscipline()));

        TableColumn<Teacher, String> tProfessionalAffiliationsColumn = new TableColumn<>("专业关联");
        tProfessionalAffiliationsColumn.setCellValueFactory(cellData -> new SimpleStringProperty(cellData.getValue().getTProfessionalAffiliations()));

        TableColumn<Teacher, String> tAdminPositionsCollegeColumn = new TableColumn<>("学院管理职务");
        tAdminPositionsCollegeColumn.setCellValueFactory(cellData -> new SimpleStringProperty(cellData.getValue().getTAdminPositionsCollege()));

        TableColumn<Teacher, String> tAcademicPositionsCollegeColumn = new TableColumn<>("学院学术职务");
        tAcademicPositionsCollegeColumn.setCellValueFactory(cellData -> new SimpleStringProperty(cellData.getValue().getTAcademicPositionsCollege()));

        teacherTableView.getColumns().addAll(tnoColumn, tTnoColumn, tCNameColumn, tSexColumn, jTnoColumn,
                tQualificationColumn, tDutiesColumn, tRemarkColumn,tSituationColumn, tEducationColumn, tFirstDisciplineColumn,
                tSecondDisciplineColumn, tProfessionalAffiliationsColumn, tAdminPositionsCollegeColumn, tAcademicPositionsCollegeColumn);
    }

    private void setupAddStudentGridPane() {
        newStudentPane.setHgap(10);
        newStudentPane.setVgap(10);

        Label snoLabel = new Label("学号:");
        Label scNameLabel = new Label("姓名:");
        Label sTypeLabel = new Label("类型:");
        Label sEnterLabel = new Label("入学年份:");
        Label stNoLabel = new Label("系编号:");
        Label satNoLabel = new Label("导师编号:");
        Label sRemarkLabel = new Label("备注:");
        Label sAccountLabel = new Label("账号:");
        Label sPasswordLabel = new Label("密码:");
        Label sInLabel = new Label("入学时间:");
        Label sOutLabel = new Label("毕业时间:");

        newStudentPane.add(snoLabel, 0, 0);
        newStudentPane.add(scNameLabel, 1, 0);
        newStudentPane.add(sTypeLabel, 2, 0);
        newStudentPane.add(sEnterLabel, 3, 0);
        newStudentPane.add(stNoLabel, 4, 0);
        newStudentPane.add(satNoLabel, 5, 0);
        newStudentPane.add(sRemarkLabel, 6, 0);
        newStudentPane.add(sAccountLabel, 7, 0);
        newStudentPane.add(sPasswordLabel, 8, 0);
        newStudentPane.add(sInLabel, 9, 0);
        newStudentPane.add(sOutLabel, 10, 0);

        newStudentPane.add(snoField, 0, 1);
        newStudentPane.add(scNameField, 1, 1);
        newStudentPane.add(sTypeField, 2, 1);
        newStudentPane.add(sEnterField, 3, 1);
        newStudentPane.add(stNoField, 4, 1);
        newStudentPane.add(satNoField, 5, 1);
        newStudentPane.add(sRemarkField, 6, 1);
        newStudentPane.add(sAccountField, 7, 1);
        newStudentPane.add(sPasswordField, 8, 1);
        newStudentPane.add(sInField, 9, 1);
        newStudentPane.add(sOutField, 10, 1);
    }

    private void setupAddTeacherGridPane() {
        newTeacherPane.setHgap(10);
        newTeacherPane.setVgap(10);

        Label tnoLabel = new Label("工号:");
        Label ttnoLabel = new Label("职工号:");
        Label tcNameLabel = new Label("姓名:");
        Label tSexLabel = new Label("性别:");
        Label jtnoLabel = new Label("职位编号:");
        Label tQualificationLabel = new Label("资历:");
        Label tDutiesLabel = new Label("职务:");
        Label tRemarkLabel = new Label("备注:");
        Label tSituationLabel = new Label("状况:");
        Label tEducationLabel = new Label("学历:");
        Label tFirstDisciplineLabel = new Label("第一学科:");
        Label tSecondDisciplineLabel = new Label("第二学科:");
        Label tProfessionalAffiliationsLabel = new Label("专业隶属:");
        Label tAdminPositionsCollegeLabel = new Label("学院行政职位:");
        Label tAcademicPositionsCollegeLabel = new Label("学院学术职位:");

        newTeacherPane.add(tnoLabel, 0, 0);
        newTeacherPane.add(ttnoLabel, 1, 0);
        newTeacherPane.add(tcNameLabel, 2, 0);
        newTeacherPane.add(tSexLabel, 3, 0);
        newTeacherPane.add(jtnoLabel, 4, 0);
        newTeacherPane.add(tQualificationLabel, 5, 0);
        newTeacherPane.add(tDutiesLabel, 6, 0);
        newTeacherPane.add(tRemarkLabel, 7, 0);
        newTeacherPane.add(tSituationLabel, 8, 0);
        newTeacherPane.add(tEducationLabel, 9, 0);
        newTeacherPane.add(tFirstDisciplineLabel, 10, 0);
        newTeacherPane.add(tSecondDisciplineLabel, 11, 0);
        newTeacherPane.add(tProfessionalAffiliationsLabel, 12, 0);
        newTeacherPane.add(tAdminPositionsCollegeLabel, 13, 0);
        newTeacherPane.add(tAcademicPositionsCollegeLabel, 14, 0);

        newTeacherPane.add(tnoField, 0, 1);
        newTeacherPane.add(ttnoField, 1, 1);
        newTeacherPane.add(tcNameField, 2, 1);
        newTeacherPane.add(tSexField, 3, 1);
        newTeacherPane.add(jtnoField, 4, 1);
        newTeacherPane.add(tQualificationField, 5, 1);
        newTeacherPane.add(tDutiesField, 6, 1);
        newTeacherPane.add(tRemarkField, 7, 1);
        newTeacherPane.add(tSituationField, 8, 1);
        newTeacherPane.add(tEducationField, 9, 1);
        newTeacherPane.add(tFirstDisciplineField, 10, 1);
        newTeacherPane.add(tSecondDisciplineField, 11, 1);
        newTeacherPane.add(tProfessionalAffiliationsField, 12, 1);
        newTeacherPane.add(tAdminPositionsCollegeField, 13, 1);
        newTeacherPane.add(tAcademicPositionsCollegeField, 14, 1);
    }

    private void showImportDialog() {
        // 设置对话框
        Stage dialog = new Stage();
        dialog.initModality(Modality.APPLICATION_MODAL);
        dialog.setTitle("导入数据");

        // 创建布局
        GridPane dialogPane = new GridPane();
        dialogPane.setHgap(10);
        dialogPane.setVgap(10);
        dialogPane.setPadding(new Insets(10));

        // 设置列约束
        ColumnConstraints column1 = new ColumnConstraints();
        column1.setHgrow(Priority.ALWAYS);
        ColumnConstraints column2 = new ColumnConstraints();
        column2.setHalignment(HPos.CENTER);
        dialogPane.getColumnConstraints().addAll(column1, column2);

        // 创建选择框
        ComboBox<String> typeChoiceBox = new ComboBox<>();
        typeChoiceBox.getItems().addAll("学生", "教师");
        typeChoiceBox.setPromptText("选择数据类型");

        // 创建导入数据按钮
        Button importButton = new Button("导入数据");
        importButton.setOnAction(e -> {
            String userType = typeChoiceBox.getValue();
            if (userType == null || userType.isEmpty()) {
                showAlert(Alert.AlertType.WARNING, "请先选择导入数据类型");
                return;
            }
            handleImport(typeChoiceBox.getValue());
        });

        // 添加选择框到布局
        dialogPane.add(typeChoiceBox, 0, 0, 2, 1);

        // 使用VBox来控制按钮位置
        VBox buttonBox = new VBox(importButton);
        buttonBox.setSpacing(30); // 设置按钮和选择框的间距
        buttonBox.setPadding(new Insets(30, 0, 0, 0)); // 上方添加一些间距
        buttonBox.setAlignment(javafx.geometry.Pos.CENTER);

        // 添加按钮到布局
        dialogPane.add(buttonBox, 0, 1, 2, 1);

        // 创建场景并设置
        Scene dialogScene = new Scene(dialogPane, 300, 150);
        dialog.setScene(dialogScene);
        dialog.show();
    }

    private void handleImport(String type) {
        FileChooser fileChooser = new FileChooser();
        fileChooser.getExtensionFilters().addAll(
                new FileChooser.ExtensionFilter("Excel Files", "*.xls", "*.xlsx"),
                new FileChooser.ExtensionFilter("CSV Files", "*.csv")
        );
        File file = fileChooser.showOpenDialog(null);
        if (file != null) {
            try {
                if (file.getName().endsWith(".csv")) {
                    importCSV(file, type);
                } else if (file.getName().endsWith(".xls") || file.getName().endsWith(".xlsx")) {
                    importExcel(file, type);
                }
                showAlert(Alert.AlertType.WARNING, "成功导入数据");
            } catch (Exception ex) {
                showAlert(Alert.AlertType.WARNING, "导入数据失败");
                ex.printStackTrace();
            }
        }
    }

    private void importCSV(File file, String type) {
        List<String[]> data = new ArrayList<>();
        try (BufferedReader br = new BufferedReader(new FileReader(file))) {
            String line;
            br.readLine();//不读表头
            while ((line = br.readLine()) != null) {
                data.add(line.split(","));
            }
        } catch (IOException e) {
            e.printStackTrace();
            return;
        }

        if (type.equals("学生")) {
            try {
                DatabaseUtil.insertStudentData(data);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        } else if (type.equals("教师")) {
            try {
                DatabaseUtil.insertTeacherData(data);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    private void importExcel(File file, String type) throws IOException, SQLException {
        FileInputStream fis = new FileInputStream(file);
        Workbook workbook = new XSSFWorkbook(fis);
        Sheet sheet = workbook.getSheetAt(0);

        List<String[]> data = new ArrayList<>();
        boolean isHeader = true;
        for (Row row : sheet) {
            if (isHeader) {
                isHeader = false;
                continue; // 跳过表头
            }
            List<String> rowData = new ArrayList<>();
            for (Cell cell : row) {
                rowData.add(cell.toString());
            }
            data.add(rowData.toArray(new String[0]));
        }
        workbook.close();
        fis.close();

        if (type.equals("学生")) {
            DatabaseUtil.insertStudentData(data);
        } else if (type.equals("教师")) {
            DatabaseUtil.insertTeacherData(data);
        }
    }

    private void showAlert(Alert.AlertType alertType, String message) {
        Alert alert = new Alert(alertType);
        alert.setContentText(message);
        alert.showAndWait();
    }

}
